package com.finance.console.dao;

import com.finance.console.entity.User;
import com.finance.console.storage.BaseDAO;
import java.sql.SQLException;
import java.text.SimpleDateFormat; // 导入日期格式化类
import java.util.ArrayList;
import java.util.List;

public class UserDAO extends BaseDAO {

    // 按用户名查询
    public User findByUsername(String username) throws SQLException {
        String sql = "select * from user where username=?";
        executeQuery(sql, username);

        if (rs.next()) {
            User user = new User();
            user.setId(rs.getInt("id")); // 映射id（int类型）
            user.setUsername(rs.getString("username")); // 映射用户名（String）
            user.setPassword(rs.getString("password")); // 映射密码（String）
            user.setRealName(rs.getString("real_name")); // 映射真实姓名（String）
            user.setRole(rs.getString("role")); // 映射角色（String）

            // 关键修改：将java.sql.Date转换为String
            java.sql.Date createTimeDate = rs.getDate("create_time");
            String createTimeStr = null;
            if (createTimeDate != null) {
                // 格式化为"yyyy-MM-dd"（如需时分秒，可改为"yyyy-MM-dd HH:mm:ss"）
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                createTimeStr = sdf.format(createTimeDate);
            }
            user.setCreateTime(createTimeStr); // 赋值转换后的字符串

            user.setStatus(rs.getInt("status")); // 映射状态（int类型）
            close();
            return user;
        }
        close();
        return null;
    }

    // 新增用户
    public int addUser(User user) throws SQLException {
        // 修正SQL：移除email字段（数据库表中不存在）
        String sql = "insert into user(username, password, real_name, role, create_time, status) " +
                "values(?, ?, ?, ?, ?, ?)";
        // 对应参数：用户名、密码、真实姓名、角色、创建时间、状态
        int rows = executeUpdate(sql,
                user.getUsername(),  // 对应getUsername()
                user.getPassword(),  // 对应getPassword()
                user.getRealName(),  // 对应getRealName()
                user.getRole(),      // 对应getRole()
                user.getCreateTime(),// 对应getCreateTime()
                user.getStatus()     // 对应getStatus()
        );
        close();
        return rows;
    }

    // 用户登录验证（新增方法）
    public User login(String username, String password) throws SQLException {
        String sql = "select * from user where username=? and password=?";
        executeQuery(sql, username, password);

        if (rs.next()) {
            User user = new User();
            user.setId(rs.getInt("id"));
            user.setUsername(rs.getString("username"));
            user.setPassword(rs.getString("password"));
            user.setRealName(rs.getString("real_name"));
            user.setRole(rs.getString("role"));

            // 处理create_time：转换为String
            java.sql.Date createTimeDate = rs.getDate("create_time");
            String createTimeStr = createTimeDate != null ?
                    new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(createTimeDate) : null;
            user.setCreateTime(createTimeStr);

            user.setStatus(rs.getInt("status"));
            close();
            return user;
        }
        close();
        return null;
    }

    public List<User> getAllUsers() throws SQLException {
        String sql = "SELECT * FROM user ORDER BY id";
        List<User> users = new ArrayList<>();
        try {
            executeQuery(sql);
            while (rs.next()) {
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                user.setRealName(rs.getString("real_name"));
                user.setRole(rs.getString("role"));
                user.setCreateTime(rs.getTimestamp("create_time").toString());
                user.setStatus(rs.getInt("status"));
                users.add(user);
            }
            return users;
        } finally {
            close();
        }
    }

    // 修改用户状态（启用/禁用）
    public int updateUserStatus(int userId, int status)throws SQLException {
        String sql = "UPDATE user SET status = ? WHERE id = ?";
        return executeUpdate(sql, status, userId);
    }

    // 删除用户（级联删除其收支和预算记录）
    public int deleteUser(int userId)throws SQLException {
        // 先删除关联数据
        String deleteExpense = "DELETE FROM income_expense WHERE userId = ?";
        String deleteBudget = "DELETE FROM budget WHERE userId = ?";
        String deleteUser = "DELETE FROM user WHERE id = ?";

        try {
            executeUpdate(deleteExpense, userId);
            executeUpdate(deleteBudget, userId);
            return executeUpdate(deleteUser, userId);
        } finally {
            close();
        }
    }
}